﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    class IncomeItemCtrl
    {
        #region 获取计划收入款项信息

        /// <summary>
        /// 根据团号获取计划收入款项信息
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static DataTable GetIncomeItemData(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘火车预定信息’表联合数据

            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "TourGroupID='" + tourGroupId + "'";

            string Sql = @"SELECT * FROM fin_IncomeItem ";

            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取团‘已收额’总和
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        internal static decimal GetIncomeItemReceivedPriceTotal(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT sum(ReceivedPrice) as ReceivedPriceTotal FROM fin_IncomeItem WHERE TourgroupID='{0}' ", tourgroupId);

            dbCommand = db.GetSqlStringCommand(Sql);

            string retValue = db.ExecuteScalar(dbCommand).ToString();
            if (!string.IsNullOrEmpty(retValue))
                return decimal.Parse(retValue);
            else
                return 0;
        }
        /// <summary>
        /// 获取团‘计划应收额’总和
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        internal static decimal GetIncomeItemPlanPriceTotal(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT sum(PlanPrice) as PlanPriceTotal FROM fin_IncomeItem WHERE TourgroupID='{0}' ", tourgroupId);

            dbCommand = db.GetSqlStringCommand(Sql);

            string retValue = db.ExecuteScalar(dbCommand).ToString();
            if (!string.IsNullOrEmpty(retValue))
                return decimal.Parse(retValue);
            else
                return 0;
        }
        /// <summary>
        /// 获取团‘调整金额’总和
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        internal static decimal GetIncomeItemAdjustPriceTotal(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT sum(AdjustPrice) as AdjustPriceTotal FROM fin_IncomeItem WHERE TourgroupID='{0}' ", tourgroupId);

            dbCommand = db.GetSqlStringCommand(Sql);

            string retValue = db.ExecuteScalar(dbCommand).ToString();
            if (!string.IsNullOrEmpty(retValue))
                return decimal.Parse(retValue);
            else
                return 0;
        }

        /// <summary>
        /// 根据计划收入款项ID获取已经‘收款录入’的数据内容
        /// </summary>
        /// <param name="incomeItemId"></param>
        /// <returns></returns>
        internal static DataTable GetIncomeData(string incomeItemId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string szCondition = "";

            if (incomeItemId != "")
                szCondition = "IncomeItemID='" + incomeItemId + "'";

            string Sql = @"SELECT [IncomeID], [IncomeItemID], [Voucher], [Currency], [ExchangeRate], [RMBAmount], [PaymentMethod], [PaymentAmount], [Time], [Transactor], [Remark] FROM fin_Income ";

            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据获得冲抵的收入
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetOffsetIncomeData()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT IncomeID,TourgroupID,TourgroupName,RMBAmount,Voucher,i.Remark  FROM  fin_IncomeItem ii,fin_Income i WHERE  PaymentMethod='冲抵' and i.IncomeItemID = ii.IncomeItemID");

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 获取团费的IncomeItem标识
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns>计划收入项的标识</returns>
//        internal static string GetTourgroupPlanIncomeItemID(string tourgroupId)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            string Sql = string.Format(@"SELECT [IncomeItemID] FROM fin_IncomeItem WHERE TourgroupID='{0}' AND CustomerName='{1}'", tourgroupId, ApplicationHelper.TourGroupPlanIncome);

//            DbCommand dbCommand = db.GetSqlStringCommand(Sql);
//            object obj = db.ExecuteScalar(dbCommand);

//            if (obj != null)
//                return obj.ToString();
//            else
//            {
//                Sql = string.Format(@"INSERT INTO fin_IncomeItem ([TourgroupID], [CustomerName], [IncomeItem], [PlanPrice], 
//                                                                    [ReceivedPrice], [AdjustPrice], [Remark])
//                                                                    VALUES('{0}', '{1}', '{2}',{3}, {4},{5},'{6}');Select SCOPE_IDENTITY();",
//                                                                    tourgroupId,
//                                                                    ApplicationHelper.TourGroupPlanIncome,
//                                                                    string.Format(@"团费({0})", tourgroupId),
//                                                                    0,
//                                                                    0,
//                                                                    0,
//                                                                    ApplicationHelper.TourGroupPlanIncome);

//                return db.ExecuteScalar(CommandType.Text, Sql).ToString();
//            }
//        }
        #endregion

        #region 计划收入款项信息的增、删、改

        internal static int CreateIncomeItemData(IncomeItemEntity incomeItem)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO fin_IncomeItem ([TourgroupID], [CustomerName], [IncomeItem], [PlanPrice], 
                                                                    [ReceivedPrice], [AdjustPrice], [Remark],[Currency],[ExchangeRate],[CurrencyAmount],[TourgroupName])
                                                                    VALUES('{0}', '{1}', '{2}',{3}, {4},{5},'{6}','{7}',{8},{9},'{10}')",
                                                                    incomeItem.TourgroupID,
                                                                    //incomeItem.CustomerID,
                                                                    incomeItem.CustomerName,
                                                                    incomeItem.IncomeItem,
                                                                    incomeItem.PlanPrice,
                                                                    incomeItem.ReceivedPrice,
                                                                    incomeItem.AdjustPrice,
                                                                    incomeItem.Remark,
                                                                    incomeItem.Currency,
                                                                    incomeItem.ExchangeRate,
                                                                    incomeItem.CurrencyAmount,
                                                                    incomeItem.TourgroupName);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 为团创建团的预算“团费”(收入项名称、计划收入费用额度)
        /// </summary>
        /// <param name="icomeItem"></param>
        //internal static int UpdateTourgroupPlanIncome(decimal planPrice, decimal ReceivedPrice, string incomeItemId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    string sql = string.Format(@"UPDATE fin_IncomeItem SET [PlanPrice]='{0}',[ReceivedPrice]={1} WHERE [IncomeItemID]={2}",
        //                                       planPrice,
        //                                       ReceivedPrice,
        //                                       incomeItemId);
        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        /// <summary>
        /// 根据IncomeItemID更新计划收入项内容
        /// </summary>
        /// <param name="incomeItem"></param>
        /// <returns></returns>
        internal static int UpdateIncomeItemData(IncomeItemEntity incomeItem)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_IncomeItem
                                              SET [IncomeItem]='{0}',[PlanPrice]={1},[Remark]='{2}',
                                                  [Currency]='{3}',[ExchangeRate]={4},[CurrencyAmount]={5}  
                                              WHERE [IncomeItemID]={6}",
                                               incomeItem.IncomeItem,
                                               incomeItem.PlanPrice,
                                               incomeItem.Remark,
                                               incomeItem.Currency,
                                               incomeItem.ExchangeRate,
                                               incomeItem.CurrencyAmount,
                                               incomeItem.IncomeItemID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新收入项‘调整价’
        /// </summary>
        /// <param name="incomeItemId"></param>
        /// <param name="adjustPrice"></param>
        /// <returns></returns>
        internal static int UpdateIncomeItemAdjustPrice(string incomeItemId, decimal adjustPrice)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_IncomeItem SET [AdjustPrice]={0} WHERE [IncomeItemID]={1}", adjustPrice,incomeItemId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 更新收入项‘已收金额’
        /// </summary>
        /// <param name="incomeItemId"></param>
        /// <param name="receivedPrice"></param>
        /// <returns></returns>
        internal static int UpdateIncomeItemReceivedPrice(string incomeItemId, decimal receivedPrice)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_IncomeItem SET [ReceivedPrice]={0} WHERE [IncomeItemID]={1}", receivedPrice, incomeItemId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteIncomeItemData(string incomeItemId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM [fin_IncomeItem] WHERE IncomeItemID={0};DELETE FROM [fin_Income] WHERE IncomeItemID={1}", incomeItemId, incomeItemId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 创建收款录入项内容
        /// </summary>
        /// <param name="income"></param>
        /// <returns>新生成的IncomeID</returns>
        internal static string CreateIncomeData(IncomeEntity income)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO fin_Income ([IncomeItemID], [Voucher], [Currency], [ExchangeRate], [RMBAmount], [PaymentMethod], [PaymentAmount], [Time], [Transactor], [Remark])
                                        VALUES({0},'{1}','{2}',{3},{4},'{5}',{6},'{7}','{8}','{9}');Select SCOPE_IDENTITY();",
                                        income.IncomeItemID,
                                        income.Voucher,
                                        income.Currency,
                                        income.ExchangeRate,
                                        income.RMBAmount,
                                        income.PaymentMethod,
                                        income.PaymentAmount,
                                        income.Time,
                                        income.Transactor,
                                        income.Remark);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }
        
        /// <summary>
        /// 根据主键IncomeID修改收款录入项内容
        /// </summary>
        /// <param name="income"></param>
        /// <returns></returns>
        internal static int UpdateIncomeData(IncomeEntity income)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_Income
                                         SET [IncomeItemID]={0}, [Voucher]='{1}', [Currency]='{2}', [ExchangeRate]={3}, [RMBAmount]={4}, [PaymentMethod]='{5}',
                                            [PaymentAmount]={6}, [Time]='{7}', [Transactor]='{8}', [Remark]='{9}' 
                                            WHERE [IncomeID]={10}",
                                           income.IncomeItemID,
                                           income.Voucher,
                                           income.Currency,
                                           income.ExchangeRate,
                                           income.RMBAmount,
                                           income.PaymentMethod,
                                           income.PaymentAmount,
                                           income.Time,
                                           income.Transactor,
                                           income.Remark,
                                           income.IncomeID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 修改摘要
        /// </summary>
        /// <param name="voucher"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdateIncomeRemark(string remark, string incomeID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_Income SET [Remark]='{0}' WHERE IncomeID={1}", remark, incomeID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 修改凭证号
        /// </summary>
        /// <param name="voucher"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdateIncomeVoucher(string voucher, string incomeID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_Income SET [Voucher]='{0}' WHERE IncomeID={1}", voucher, incomeID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 更新款项
        /// </summary>
        /// <param name="IncomeItem"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdateIncomeItem(string incomeItem, string incomeItemID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_IncomeItem SET [IncomeItem]='{0}' WHERE IncomeItemID = {1}", incomeItem, incomeItemID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }


        /// <summary>
        /// 更新支付单ID和支付状态，用于批量付款中的冲抵操作
        /// </summary>
        /// <param name="IncomeItem"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdateIncomePayId(string payId, string incomeID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_Income SET [PayID]='{0}',[PaymentMethod]='冲抵完成' WHERE incomeID = {1}", payId, incomeID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 根据主键IncomeID删除计划收入项
        /// </summary>
        /// <param name="incomeId"></param>
        /// <returns></returns>
        //internal static int DeleteIncomeData(string incomeId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"DELETE FROM [fin_Income] WHERE IncomeID={0}",incomeId);
        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        #endregion

    }
}
